package pers.ht.mbpo.dao;


import pers.ht.mbpo.bean.BusEnvironment;
import pers.ht.mbpo.util.DBUtil;
import pers.ht.mbpo.util.DaoUtil;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class BusEnvironmentDao {
    //添加
    public void insert(BusEnvironment b) {
        if(!b.getTime().equals("08:00")){
        String sql = "INSERT INTO bus_environment(time,busLineName,updown,keypoint,pointName,pointLng,pointLat,Angle,crosstype,distanceNext,traffic,flash) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
        DaoUtil.update(sql, b.getTime(),b.getBusLineName(),b.getUpdown(),b.getKeypoint(),b.getPointName(),b.getPointLng(),b.getPointLat(),b.getAngle(),b.getCrosstype(),b.getDistanceNext(),b.getTraffic(),b.getFlash());
    }
    }

    //删除
    public void delete() {
        String sql = "DELETE FROM bus_environment where time <> '08:00'";
        DaoUtil.update(sql);
    }

    //查询所有
    public List<BusEnvironment> query(String time, String busLineName, String updown) {
        List<BusEnvironment> busEnvironments = new ArrayList<BusEnvironment>();
        String sql = "SELECT * from bus_environment  WHERE 1=1 ";
        if (time != null) {
            sql += " AND time = '" + time + "' ";
        }
        if (updown != null) {
            sql += " AND updown = '" + updown + "' ";
        }
        if (busLineName != null) {
            try {
                String strB=new String(busLineName.getBytes("ISO-8859-1"), "UTF-8");
                sql += " AND busLineName = '" + strB + "' ";
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }

        }

        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            stat = conn.createStatement();
            rs = stat.executeQuery(sql);
            while (rs.next()) {
                BusEnvironment b = new BusEnvironment();
                b.setTime(rs.getString("time"));
                b.setBusLineName(rs.getString("busLineName"));
                b.setUpdown(rs.getString("updown"));
                b.setKeypoint(rs.getString("keypoint"));
                b.setPointName(rs.getString("pointName"));
                b.setPointLng(rs.getString("pointLng"));
                b.setPointLat(rs.getString("pointLat"));
                b.setAngle(rs.getString("Angle"));
                b.setCrosstype(rs.getString("crosstype"));
                b.setDistanceNext(rs.getString("distanceNext"));
                b.setTraffic(rs.getString("traffic"));
                b.setFlash(rs.getString("flash"));

                busEnvironments.add(b);
            }
            DBUtil.close(rs, stat, conn);
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        return busEnvironments;
    }

    public List<BusEnvironment> queryEdge(String time,String busLineName, String updown) {
        List<BusEnvironment> busEnvironments = new ArrayList<BusEnvironment>();
        String sql = "SELECT e.*,k.* from bus_environment e JOIN bus_edge_keypoint k ON e.keypoint=k.keypoint AND e.busLineName=k.busLineName AND e.updown=k.updown WHERE 1=1";
        if (time != null) {
            sql += " AND e.time = '" + time + "' ";
        }
        if (updown != null) {
            sql += " AND e.updown = '" + updown + "' ";
        }
        if (busLineName != null) {
            try {
                String strB=new String(busLineName.getBytes("ISO-8859-1"), "UTF-8");
                sql += " AND e.busLineName = '" + strB + "' ";
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }

        }

        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            stat = conn.createStatement();
            rs = stat.executeQuery(sql);
            while (rs.next()) {
                BusEnvironment b = new BusEnvironment();
                b.setTime(rs.getString("time"));
                b.setBusLineName(rs.getString("e.busLineName"));
                b.setUpdown(rs.getString("e.updown"));
                b.setKeypoint(rs.getString("e.keypoint"));
                b.setPointName(rs.getString("pointName"));
                b.setPointLng(rs.getString("pointLng"));
                b.setPointLat(rs.getString("pointLat"));
                b.setAngle(rs.getString("Angle"));
                b.setCrosstype(rs.getString("crosstype"));
                b.setDistanceNext(rs.getString("distanceNext"));
                b.setTraffic(rs.getString("traffic"));
                b.setFlash(rs.getString("flash"));
                b.setLnglat(rs.getString("k.lnglat"));
                busEnvironments.add(b);
            }
            DBUtil.close(rs, stat, conn);
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        return busEnvironments;
    }
}
